﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HenIT.Data.SqlClient;
using System.Data.SqlClient;
using System.Data;

namespace EventScavenger
{
	public partial class EventScavengerDAL : GenericSQLServerDAL
	{
		public EventScavengerDAL(): base()
		{
			maxCount = 50;
			untilDate = DateTime.Now;
		}

		#region Properties
		//****** Filters ******
		private int maxCount;
		public int MaxCount
		{
			get { return maxCount; }
			set { maxCount = value; }
		}
		private string messageFilter = "";
		public string MessageFilter
		{
			get { return messageFilter; }
			set { messageFilter = value; }
		}

		private DateTime fromDate;
		public DateTime FromDate
		{
			get { return fromDate; }
			set { fromDate = value; }
		}
		private DateTime untilDate;
		public DateTime UntilDate
		{
			get { return untilDate; }
			set { untilDate = value; }
		}
		private EventLogEntryTypes eventLogEntryType = EventLogEntryTypes.All;
		public EventLogEntryTypes EventLogEntryType
		{
			get { return eventLogEntryType; }
			set { eventLogEntryType = value; }
		}

		#region Event IDs
		private bool viewAllEventIDs = true;
		public bool ViewAllEventIDs
		{
			get { return viewAllEventIDs; }
			set { viewAllEventIDs = value; }
		}
		private List<int> eventIdsVisible = new List<int>();
		public List<int> EventIdsVisible
		{
			get { return eventIdsVisible; }
			set { eventIdsVisible = value; }
		}
		private List<int> availableEventIDs;
		public List<int> AvailableEventIDs
		{
			get { return availableEventIDs; }
			set { availableEventIDs = value; }
		} 
		#endregion

		#region Event sources
		private bool showAllSources = true;
		public bool ShowAllSources
		{
			get { return showAllSources; }
			set { showAllSources = value; }
		}
		private List<string> sourceFilters = new List<string>();
		public List<string> SourceFilters
		{
			get { return sourceFilters; }
			set { sourceFilters = value; }
		}
		private List<string> availableSources;
		public List<string> AvailableSources
		{
			get { return availableSources; }
			set { availableSources = value; }
		} 
		#endregion

		#region Machine/logs
		private bool showAllMachineLogs = true;
		public bool ShowAllMachineLogs
		{
			get { return showAllMachineLogs; }
			set { showAllMachineLogs = value; }
		}
		private List<MachineLogEntry> machineLogFilters = new List<MachineLogEntry>();
		public List<MachineLogEntry> MachineLogFilters
		{
			get { return machineLogFilters; }
			set { machineLogFilters = value; }
		}
		private List<MachineLogEntry> availableMachineLog;
		public List<MachineLogEntry> AvailableMachineLog
		{
			get { return availableMachineLog; }
			set { availableMachineLog = value; }
		}
		private bool viewDisabledMachineLogs;
		public bool ViewDisabledMachineLogs
		{
			get { return viewDisabledMachineLogs; }
			set { viewDisabledMachineLogs = value; }
		}
		#endregion

		#endregion

		#region Test connection
		internal int TestConnection()
		{
			string sql = "GetEventlogRowCount";
			return (int)GetSingleValue(sql);
		}
		internal double GetDatabaseVersion()
		{
			double version;
			string sql = "GetSettingValue";
			SqlParameter[] parms = new SqlParameter[] {
					new SqlParameter("@SettingName", "DatabaseVersion")
				};
            version = double.Parse(GetSingleValue(sql, CommandType.StoredProcedure, parms).ToString(), System.Globalization.CultureInfo.InvariantCulture);
			return version;
		}
		internal int TestConnectionGetEntryCount()
		{
			string sql = "GetEventlogRowCount";
			return (int)GetSingleValue(sql);
		}
		#endregion

		#region Stats - note - currently using direct sql text - investigate convert to sproc
		public List<PeriodCount> GetCountsPerPeiodHistoric(
				CountsPerPeriodType countsPerPeriod,
				EventLogEntryTypes eventLogEntryTypes,
				DateTime from, DateTime to,
				List<MachineLogEntry> logs,
				List<int> eventIDs,
				List<string> sources
			)
		{
			List<PeriodCount> results = new List<PeriodCount>();
			string groupby;
			string where = "";
			string sql = "select ";
			switch (countsPerPeriod)
			{
				case CountsPerPeriodType.Total:
					groupby = "";
					break;
				case CountsPerPeriodType.Minute:
					groupby = "[Year], [Month], [Day], [Hour] ";
					break;
				case CountsPerPeriodType.Hour:
					groupby = "[Year], [Month], [Day], [Hour] ";
					break;
				case CountsPerPeriodType.Day:
					groupby = "[Year], [Month], [Day] ";
					break;
				case CountsPerPeriodType.Week:
					groupby = "[Year], DatePart(wk, cast(cast([Year] as char(4)) + '-' + cast([Month] as char(2)) + '-' + cast([Day] as char(2)) as datetime)) ";
					break;
				case CountsPerPeriodType.Month:
					groupby = "[Year], [Month] ";
					break;
				default:
					groupby = "";
					break;
			}
			where = "WHERE cast(cast([Year] as char(4)) + '-' + cast([Month] as char(2)) + '-' + cast([Day] as char(2)) as datetime) between '" + from.ToString("yyyy-MM-dd HH:mm") + "' and '" + to.ToString("yyyy-MM-dd HH:mm") + "' ";
			switch (eventLogEntryTypes)
			{
				case EventLogEntryTypes.Errors:
					where += " and (EventTypeName like 'E%') ";
					break;
				case EventLogEntryTypes.Warnings:
					where += " and (EventTypeName like 'W%') ";
					break;
				case EventLogEntryTypes.ErrorsAndWarnings:
					where += " and ((EventTypeName like 'E%') or (EventTypeName like 'W%')) ";
					break;
				case EventLogEntryTypes.Information:
					where += " and (EventTypeName like 'I%') ";
					break;
				default:
					break;
			}
			if (logs.Count > 0)
			{
				where += " and (";
				foreach (MachineLogEntry mle in logs)
				{
					where += " (m.Machine like '" + mle.Machine.Replace("'", "''") + "' and m.LogName like '" + mle.LogName + "') or ";
				}
				where = where.Trim('o', 'r', ' ') + ") ";
			}
			if (eventIDs.Count > 0)
			{
				where += " and EventID in (";
				foreach (int eventId in eventIDs)
				{
					where += eventId.ToString() + ",";
				}
				where = where.Trim(',') + ") ";
			}
			if (sources.Count > 0)
			{
				where += " and s.Source in (";
				foreach (string source in sources)
				{
					where += "'" + source.Replace("'", "''") + "',";
				}
				where = where.Trim(',') + ") ";
			}
			if (groupby.Length > 0)
			{
				sql += groupby + ", sum(Records) FROM MachineLogs m inner join EventLogHistory h on m.MachineLogID = h.MachineLogID inner join EventSources s on h.SourceID = s.SourceID " + where;
				sql += "GROUP BY " + groupby;
				sql += "ORDER BY " + groupby;
			}
			else
				sql += " sum(Records) FROM MachineLogs m inner join EventLogHistory h on m.MachineLogID = h.MachineLogID inner join EventSources s on h.SourceID = s.SourceID " + where;

			using (SqlConnection conn = new SqlConnection(connectionString))
			{
				conn.Open();
				using (SqlCommand cmnd = new SqlCommand(sql, conn))
				{
					cmnd.CommandTimeout = CommandTimeout;
					using (SqlDataReader dr = cmnd.ExecuteReader())
					{
						while (dr.Read())
						{
							PeriodCount periodCount;

							switch (countsPerPeriod)
							{
								case CountsPerPeriodType.Total:
									periodCount.PeriodStart = from;
									break;
								case CountsPerPeriodType.Minute:
									periodCount.PeriodStart = new DateTime((int)dr[0], byte.Parse(dr[1].ToString()), byte.Parse(dr[2].ToString()), byte.Parse(dr[3].ToString()), 0, 0);
									break;
								case CountsPerPeriodType.Hour:
									periodCount.PeriodStart = new DateTime((int)dr[0], byte.Parse(dr[1].ToString()), byte.Parse(dr[2].ToString()), byte.Parse(dr[3].ToString()), 0, 0);
									break;
								case CountsPerPeriodType.Day:
									periodCount.PeriodStart = new DateTime((int)dr[0], byte.Parse(dr[1].ToString()), byte.Parse(dr[2].ToString()));
									break;
								case CountsPerPeriodType.Week:
									periodCount.PeriodStart = new DateTime((int)dr[0], 1, 1);
									periodCount.PeriodStart = periodCount.PeriodStart.AddDays(7 * byte.Parse(dr[1].ToString()));
									break;
								case CountsPerPeriodType.Month:
									periodCount.PeriodStart = new DateTime((int)dr[0], byte.Parse(dr[1].ToString()), 1);
									break;
								default:
									periodCount.PeriodStart = from;
									break;
							}
							if (dr[dr.FieldCount - 1] != System.DBNull.Value)
								periodCount.Count = (int)dr[dr.FieldCount - 1];
							else
								periodCount.Count = 0;
							results.Add(periodCount);
						}
					}
				}
			}

			return results;
		}
		public List<PeriodCount> GetCountsPerPeiod(
				CountsPerPeriodType countsPerPeriod,
				EventLogEntryTypes eventLogEntryTypes,
				DateTime from, DateTime to,
				List<MachineLogEntry> logs,
				List<int> eventIDs,
				List<string> sources
			)
		{
			List<PeriodCount> results = new List<PeriodCount>();
			string groupby;
			string where = "";
			string sql = "select ";
			switch (countsPerPeriod)
			{
				case CountsPerPeriodType.Total:
					groupby = "";
					break;
				case CountsPerPeriodType.Minute:
					groupby = "Year(TimeGenerated), Month(TimeGenerated), Day(TimeGenerated), DatePart(hh, TimeGenerated), DatePart(mi, TimeGenerated) ";
					break;
				case CountsPerPeriodType.Hour:
					groupby = "Year(TimeGenerated), Month(TimeGenerated), Day(TimeGenerated), DatePart(hh, TimeGenerated) ";
					break;
				case CountsPerPeriodType.Day:
					groupby = "Year(TimeGenerated), Month(TimeGenerated), Day(TimeGenerated) ";
					break;
				case CountsPerPeriodType.Week:
					groupby = "Year(TimeGenerated), DatePart(wk, TimeGenerated) ";
					break;
				case CountsPerPeriodType.Month:
					groupby = "Year(TimeGenerated), Month(TimeGenerated) ";
					break;
				default:
					groupby = "";
					break;
			}
			where = "WHERE TimeGenerated between '" + from.ToString("yyyy-MM-dd HH:mm") + "' and '" + to.ToString("yyyy-MM-dd HH:mm") + "' ";
			switch (eventLogEntryTypes)
			{
				case EventLogEntryTypes.Errors:
					where += " and (EventTypeName like 'E%') ";
					break;
				case EventLogEntryTypes.Warnings:
					where += " and (EventTypeName like 'W%') ";
					break;
				case EventLogEntryTypes.ErrorsAndWarnings:
					where += " and ((EventTypeName like 'E%') or (EventTypeName like 'W%')) ";
					break;
				case EventLogEntryTypes.Information:
					where += " and (EventTypeName like 'I%') ";
					break;
				default:
					break;
			}
			if (logs.Count > 0)
			{
				where += " and (";
				foreach (MachineLogEntry mle in logs)
				{
					where += " (ComputerName like '" + mle.Machine.Replace("'", "''") + "' and EventLog like '" + mle.LogName + "') or ";
				}
				where = where.Trim('o', 'r', ' ') + ") ";
			}
			if (eventIDs.Count > 0)
			{
				where += " and EventID in (";
				foreach (int eventId in eventIDs)
				{
					where += eventId.ToString() + ",";
				}
				where = where.Trim(',') + ") ";
			}
			if (sources.Count > 0)
			{
				where += " and SourceName in (";
				foreach (string source in sources)
				{
					where += "'" + source.Replace("'", "''") + "',";
				}
				where = where.Trim(',') + ") ";
			}

			if (groupby.Length > 0)
			{
				sql += groupby + ", count(*) FROM EventLog " + where;
				sql += "GROUP BY " + groupby;
				sql += "ORDER BY " + groupby;
			}
			else
				sql += " count(*) FROM EventLog " + where;

			using (SqlConnection conn = new SqlConnection(connectionString))
			{
				conn.Open();
				using (SqlCommand cmnd = new SqlCommand(sql, conn))
				{
					;
					cmnd.CommandTimeout = CommandTimeout;
					using (SqlDataReader dr = cmnd.ExecuteReader())
					{
						while (dr.Read())
						{
							PeriodCount periodCount;

							switch (countsPerPeriod)
							{
								case CountsPerPeriodType.Total:
									periodCount.PeriodStart = from;
									break;
								case CountsPerPeriodType.Minute:
									periodCount.PeriodStart = new DateTime((int)dr[0], (int)dr[1], (int)dr[2], (int)dr[3], (int)dr[4], 0);
									break;
								case CountsPerPeriodType.Hour:
									periodCount.PeriodStart = new DateTime((int)dr[0], (int)dr[1], (int)dr[2], (int)dr[3], 0, 0);
									break;
								case CountsPerPeriodType.Day:
									periodCount.PeriodStart = new DateTime((int)dr[0], (int)dr[1], (int)dr[2]);
									break;
								case CountsPerPeriodType.Week:
									periodCount.PeriodStart = new DateTime((int)dr[0], 1, 1);
									periodCount.PeriodStart = periodCount.PeriodStart.AddDays(7 * (int)dr[1]);
									break;
								case CountsPerPeriodType.Month:
									periodCount.PeriodStart = new DateTime((int)dr[0], (int)dr[1], 1);
									break;
								default:
									periodCount.PeriodStart = from;
									break;
							}
							periodCount.Count = (int)dr[dr.FieldCount - 1];
							results.Add(periodCount);
						}
					}
				}
			}

			return results;
		}
		#endregion

		#region Get lists for log, event id and source filters
		public List<MachineLogEntry> GetMachineLogList(bool viewDisabledLogs)
		{
			List<MachineLogEntry> list = new List<MachineLogEntry>();
			availableMachineLog = new List<MachineLogEntry>();
			string sql = "SelectMachineLogList3";
			SqlParameter[] parameters = new SqlParameter[] 
			{
				new SqlParameter("@showDisabled", viewDisabledLogs)
			};
			DataSet dsMle = base.GetDataSet(sql, CommandType.StoredProcedure, parameters);
			if (dsMle.Tables.Count > 0)
			{
				list.AddRange(
					from DataRow r in dsMle.Tables[0].Rows
					select new MachineLogEntry(r["Machine"].ToString(), r["LogName"].ToString())
					{
						MachineLogID = (int)r["MachineLogID"],
						Enabled = (bool)r["Enabled"],
						PollFrequency = (int)r["PollFrequency"],
						LastPollTime = (DateTime)(r["LastPollTime"] == DBNull.Value ? new DateTime(2000, 1, 1) : r["LastPollTime"])
					}
					);
				availableMachineLog.AddRange(
						from DataRow r in dsMle.Tables[0].Rows
						select new MachineLogEntry(r["Machine"].ToString(), r["LogName"].ToString())
						{
							MachineLogID = (int)r["MachineLogID"],
							Enabled = (bool)r["Enabled"],
							PollFrequency = (int)r["PollFrequency"],
							LastPollTime = (DateTime)(r["LastPollTime"] == DBNull.Value ? new DateTime(2000, 1, 1) : r["LastPollTime"])
						}
					);
			}

			return list;
		}
		public List<int> GetEventIDList()
		{
			List<int> list = new List<int>();
			availableEventIDs = new List<int>();
			string sql = "SelectEventIDList";
			DataSet dsSources = base.GetDataSet(sql, CommandType.StoredProcedure);
			if (dsSources.Tables.Count > 0)
			{
				list.AddRange(
					from DataRow r in dsSources.Tables[0].Rows
					select (int)r[0]
					);
				availableEventIDs.AddRange(
					from DataRow r in dsSources.Tables[0].Rows
					select (int)r[0]
					);
			}

			return list;
		}
		public List<string> GetSourceList()
		{
			List<string> list = new List<string>();
			availableSources = new List<string>();
			string sql = "SelectSourceList";
			DataSet dsSources = base.GetDataSet(sql, CommandType.StoredProcedure);
			if (dsSources.Tables.Count > 0)
			{
				list.AddRange(
					from DataRow r in dsSources.Tables[0].Rows
					select r[0].ToString()
					);
				availableSources.AddRange(
					from DataRow r in dsSources.Tables[0].Rows
					select r[0].ToString()
					);
			}

			return list;
		} 
		#endregion

		#region Main get list method
		public List<EventEntry> GetEntries()
		{
			List<EventEntry> entries = new List<EventEntry>();
			string sql = "SelectEventLogEntries2";
			SqlParameter[] parameters = new SqlParameter[9];
			parameters[0] = new SqlParameter("@RowCount", maxCount);
			parameters[1] = new SqlParameter("@FromDate", fromDate);
			parameters[2] = new SqlParameter("@ToDate", untilDate);
			if (eventLogEntryType == EventLogEntryTypes.All)
				parameters[3] = new SqlParameter("@EventTypeName", DBNull.Value);
			else
				parameters[3] = new SqlParameter("@EventTypeName", GetEventLogEntryTypeString(eventLogEntryType));

			parameters[4] = new SqlParameter("@ViewDisabledMachineLogs", viewDisabledMachineLogs ? 1 : 0);
			if (showAllMachineLogs)
				parameters[5] = new SqlParameter("@MachineLogFilter", DBNull.Value);
			else
				parameters[5] = new SqlParameter("@MachineLogFilter", ListToCSV(machineLogFilters));
			if (viewAllEventIDs)
				parameters[6] = new SqlParameter("@EventIdFilter", DBNull.Value);
			else
				parameters[6] = new SqlParameter("@EventIdFilter", ListToCSV(eventIdsVisible));
			if (showAllSources)
				parameters[7] = new SqlParameter("@SourceFilters", DBNull.Value);
			else
				parameters[7] = new SqlParameter("@SourceFilters", ListToCSV(sourceFilters));
			if (messageFilter.Length <= 2)
				parameters[8] = new SqlParameter("@MessageFilter", DBNull.Value);
			else
				parameters[8] = new SqlParameter("@MessageFilter", "%" + messageFilter.Replace("'", "''") + "%");

			DataSet dsEntries = base.GetDataSet(sql, CommandType.StoredProcedure, parameters);
			if (dsEntries.Tables.Count > 0)
			{
				entries.AddRange(
					from DataRow r in dsEntries.Tables[0].Rows
					select new EventEntry()
					{
						MachineLogId = (int)r["MachineLogID"],
						MachineName = r["ComputerName"].ToString(),
						LogName = r["EventLog"].ToString(),
						RecordNumber = (int)r["RecordNumber"],
						EventID = (int)r["EventID"],
						TimeGenerated = (DateTime)r["TimeGenerated"],
						SourceName = r["SourceName"].ToString(),
						EventTypeName = GetEventTypeString(r["EventTypeName"].ToString()),
						Message = r["Message"].ToString(),
						Category = r["Category"].ToString(),
						UserName = r["UserName"].ToString(),
						Repeat = (int)r["Repeat"]
					}
					);
			}
			return entries;
		} 
		#endregion

		#region Helper methods
		private string GetEventLogEntryTypeString(EventLogEntryTypes eventLogEntryTypes)
		{
			switch (eventLogEntryType)
			{
				case EventLogEntryTypes.Errors:
					return "E,F";
				case EventLogEntryTypes.Warnings:
					return "W";
				case EventLogEntryTypes.ErrorsAndWarnings:
					return "E,W,F";
				case EventLogEntryTypes.Information:
					return "I,S";
				default:
					return "";
			}
		}
		private string ListToCSV(List<int> list)
		{
			string output = string.Empty;
			foreach (int element in list)
			{
				output += element.ToString() + ",";
			}
			output = output.TrimEnd(',');
			return output;
		}
		private string ListToCSV(List<MachineLogEntry> list)
		{
			string output = string.Empty;
			foreach (MachineLogEntry element in list)
			{
				output += element.Machine + '\\' + element.LogName + ",";
			}
			output = output.TrimEnd(',');
			return output;
		}
		private string ListToCSV(List<string> list)
		{
			string output = string.Empty;
			foreach (string element in list)
			{
				output += element + ",";
			}
			output = output.TrimEnd(',');
			return output;
		}
		private string GetEventTypeString(string eventType)
		{
			switch (eventType.Substring(0, 1))
			{
				case "E":
					return "Error";
				case "W":
					return "Warning";
				case "F":
					return "Failure";
				case "S":
					return "Success";
				default:
					return "Information";
			}
		}
		#endregion
	}
}
